﻿CREATE PROCEDURE [dbo].[utl_AddTimestamp]
@Table [sysname]=NULL, @Force BIT=0
AS
BEGIN
	DECLARE @True  AS BIT; SET @True  = 1
	DECLARE @False AS BIT; SET @False = 0
	
	DECLARE @SQL           NVARCHAR(MAX)
	DECLARE @ErrorCode     INT
	DECLARE @RowsAffected  INT
	DECLARE @Message       VARCHAR(MAX)
	DECLARE @RC            INT
	
	-- see if table name was passed
	IF @Table IS NULL
	BEGIN
	    SET @Message = 'Must pass @Table parameter.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 1
	END
	
	-- see if table exists
	IF dbo.fn_TableExists(@Table) = @False
	BEGIN
	    SET @Message = 'Table ''' + @Table + ''' not found.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 2
	END
	
	-- remove existing timestamp column before continuing
	IF @Force = @True
	BEGIN
	    EXECUTE @RC = [dbo].[utl_RemoveTimestamp] @Table
	    IF @RC <> 0
	    BEGIN
	        SET @Message = 'Could not execute utl_RemoveTimestamp for table ''' + @Table + '''.'
	        RAISERROR(@Message, 16, 1)
	        RETURN 3
	    END
	END
	
	-- check for existing timestamp column
	IF dbo.fn_TableHasTimestamp(@Table) = @True
	BEGIN
	    SET @Message = 'Table ''' + @Table + ''' already has a timestamp column.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 4
	END
	
	-- disable triggers for table
	SET @SQL = 'ALTER TABLE ' + @Table + ' DISABLE TRIGGER ALL'
	EXECUTE sp_executesql @SQL
	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Could not disable triggers for table ''' + @Table + '''.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 5
	END
	
	-- add timestamp column to the table
	IF dbo.fn_ColumnExists(@Table, 'TimeStmp') = @False
	BEGIN
	    SET @SQL = 'ALTER TABLE ' + @Table + ' ADD TimeStmp TIMESTAMP NULL'
	    EXECUTE sp_executesql @SQL
	    SELECT @ErrorCode = @@ERROR,
	           @RowsAffected = @@ROWCOUNT
	    IF @ErrorCode <> 0
	    BEGIN
	        SET @Message = 'Could not alter table ''' + @Table + '''. Could not add column ''TimeStmp''.'
	        RAISERROR(@Message, 16, 1)
	        RETURN 6
	    END
	END
	
	-- enable triggers for table
	SET @SQL = 'ALTER TABLE ' + @Table + ' ENABLE TRIGGER ALL'
	EXECUTE sp_executesql @SQL
	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Could not enable triggers for table ''' + @Table + '''.' 
	    RAISERROR(@Message, 16, 1)
	    RETURN 7
	END
	
	RETURN 0
END

